/*
 *  Copyright 2011 Alexey Andreev.
 * 
 *  Licensed under the Apache License, Version 2.0 (the "License");
 *  you may not use this file except in compliance with the License.
 *  You may obtain a copy of the License at
 * 
 *       http://www.apache.org/licenses/LICENSE-2.0
 * 
 *  Unless required by applicable law or agreed to in writing, software
 *  distributed under the License is distributed on an "AS IS" BASIS,
 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *  See the License for the specific language governing permissions and
 *  limitations under the License.
 *  under the License.
 */
package org.xthl.wiki.data;

import org.nop.migration.AbstractMigration;
import org.nop.migration.ChangeSetBuilder;
import org.nop.migration.Nonreversible;

/**
 *
 * @author Alexey Andreev
 */
@Nonreversible
public class Migration0 extends AbstractMigration {
    @Override
    protected void apply(ChangeSetBuilder cs) {
        createArticleTables(cs);
        createCategoryTables(cs);
        createCommentTables(cs);
        createResourceTables(cs);
        createHistoryTables(cs);
    }
    
    private void createArticleTables(ChangeSetBuilder cs) {
        cs.createSequence("Articles_sequence", 1);
        cs.createTable("Articles")
                .with().column("id").integer().primaryKey()
                .with().column("lastRevisionId").integer().nullable().indexed()
                .with().column("lastPublishedRevisionId").integer().nullable().indexed()
                .with().column("creationDate").timestamp().indexed()
                .with().column("publicationDate").timestamp().nullable().indexed()
                .with().column("title").varchar(200)
                .with().column("deleted").bool().defaultValue(false);
        cs.createTable("Articles_text")
                .with().column("articleId").integer().reference().to("Articles", "id")
                .with().column("articleText").text()
                .with().primaryKey().on("articleId");
        
        cs.createSequence("Revisions_sequence", 1);
        cs.createTable("Revisions")
                .with().column("id").integer().primaryKey()
                .with().column("articleId").integer().reference().to("Articles", "id")
                .with().column("title").varchar(200)
                .with().column("number").integer()
                .with().column("previousRevisionId").integer().nullable().reference()
                    .to("Revisions", "id")
                .with().column("mergedRevisionId").integer().nullable().reference()
                    .to("Revisions", "id").defaultValue(null)
                .with().column("authorId").integer().reference().to("Users", "id")
                .with().column("revisionDate").timestamp().indexed()
                .with().column("note").text().defaultValue("")
                .with().unique("Revisions_unique").columns("id", "number");
        cs.createTable("Revisions_text")
                .with().column("revisionId").integer().reference().to("Revisions", "id")
                .with().column("revisionText").text()
                .with().primaryKey().on("revisionId");
    }
    
    private void createCategoryTables(ChangeSetBuilder cs) {
        cs.createSequence("Categories_sequence", 2);
        cs.createTable("Categories")
                .with().column("id").integer().primaryKey()
                .with().column("title").varchar(200)
                .with().column("leftBound").integer().indexed()
                .with().column("rightBound").integer().indexed()
                .with().column("deleted").bool().defaultValue(false);
        cs.sql("INSERT INTO Categories (id, title, leftBound, rightBound, deleted) " +
        		"VALUES (1, '', 0, 1, '0')");
        
        cs.createTable("Categories_Articles")
                .with().column("categoryId").integer().reference().to("Categories", "id")
                .with().column("articleId").integer().reference().to("Articles", "id")
                .with().primaryKey().on("categoryId", "articleId");
    }
    
    private void createCommentTables(ChangeSetBuilder cs) {
        cs.createSequence("Comments_sequence", 1);
        cs.createTable("Comments")
                .with().column("id").integer().primaryKey()
                .with().column("articleId").integer().reference().to("Articles", "id")
                .with().column("commentDate").timestamp().indexed()
                .with().column("authorId").integer().reference().to("Users", "id")
                .with().column("deleted").bool().defaultValue(false);
        cs.createTable("Comments_text")
                .with().column("commentId").integer().reference().to("Comments", "id")
                .with().column("commentText").text()
                .with().primaryKey().on("commentId");
    }
    
    private void createResourceTables(ChangeSetBuilder cs) {
        cs.createSequence("Resources_sequence", 1);
        cs.createTable("Resources")
                .with().column("id").integer().primaryKey()
                .with().column("name").varchar(80)
                .with().column("articleId").integer().reference().to("Articles", "id")
                .with().column("title").varchar(200)
                .with().column("deleted").bool().defaultValue(false)
                .with().index("Resources_name").on("articleId", "name");
        cs.createTable("Resources_data")
                .with().column("resourceId").integer().reference().to("Resources", "id")
                .with().column("resourceData").blob()
                .with().primaryKey().on("resourceId");
    }
    
    private void createHistoryTables(ChangeSetBuilder cs) {
        cs.createSequence("History_sequence", 1);
        cs.createTable("History")
                .with().column("id").integer().primaryKey()
                .with().column("articleId").integer().nullable().reference()
                    .to("Articles", "id").defaultValue(null)
                .with().column("revisionId").integer().nullable().reference()
                    .to("Revisions", "id").defaultValue(null)
                .with().column("categoryId").integer().nullable().reference()
                    .to("Categories", "id").defaultValue(null)
                .with().column("resourceId").integer().nullable().reference()
                    .to("Resources", "id").defaultValue(null)
                .with().column("commentId").integer().nullable().reference()
                    .to("Comments", "id").defaultValue(null)
                .with().column("actionType").varchar(10).check().values(
                        "created", "modified", "published", "deleted", "restored")
                .with().column("userId").integer().reference().to("Users", "id")
                .with().column("actionDate").timestamp().indexed();
    }
}
